﻿
Option Strict On
Option Explicit On

Imports System.Data.Odbc
Imports Oracle.ManagedDataAccess.Client

Public Class clsOdbcDbIf
    ''' 
    ''' SQLコネクション
    ''' 
    Private _con As OracleConnection = Nothing
    ''' 
    ''' トランザクション・オブジェクト
    ''' 
    ''' 
    Private _trn As OracleTransaction = Nothing

    '''
    ''' 接続フラグ
    ''' 
    Private _connflg As Boolean = False

    Private SqlCmd As OracleCommand

    Private WithEvents ConnectTimer As New Timer

    'Disconnect Timer Interval (Min)
    '>>2018/02/20 安部 切断タイマを秒指定に変更
    'Private Const MINUTS_TO_DISCONNECT = 1
    Private Const SECONDS_TO_DISCONNECT = 60
    '<<2018/02/20 安部 切断タイマを秒指定に変更

    Private it As Integer = 0


    Public Sub New()
        ConnectTimer.Interval = 1000  '1000ms毎にタイマ処理実行
        ConnectTimer.Stop()
    End Sub

    ''' 
    ''' 
    ''' DB接続
    ''' 
    ''' データソース名
    ''' データベース名
    ''' ユーザーID
    ''' パスワード
    ''' タイムアウト値
    ''' 
    Public Function Connect(Optional ByVal drv As String = "",
                            Optional ByVal IPadr As String = "",
                            Optional ByVal dsn As String = "",
                            Optional ByVal dbn As String = "atlas",
                            Optional ByVal uid As String = "atlas",
                            Optional ByVal pas As String = "atlas",
                            Optional ByVal tot As Integer = -1,
                            Optional ByVal port As Integer = 1521) As Integer

        Dim szMess As String

        _connflg = False
        Connect = 0
        g_szDBIpAddress = ""

        Try
            If _con Is Nothing Then
                '_con = New OdbcConnection
                _con = New OracleConnection
            End If

            Dim cst As String = ""
            'DEL MIAO 2018/10/17 BEGIN
            'cst = cst & "Driver={" & drv & "}"

            'cst = cst & ";Server=" & IPadr
            ''cst = cst & ";DSN=" & dsn
            'cst = cst & ";Database=" & dbn
            'cst = cst & ";UID=" & uid
            'cst = cst & ";PWD=" & pas

            'If tot > -1 Then
            '    '_con.ConnectionTimeout = tot
            '    cst = cst & ";Connect Timeout=" & tot.ToString
            'End If
            'DEL MIAO 2018/10/17 END
            'ADD MIAO 2018/10/17 BEGIN
            'cst = "User Id=atlas;Password=atlas;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.143.125.93)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orac)))"
            cst = "User Id=" & uid
            cst = cst & ";Password=" & pas
            cst = cst & ";Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" & IPadr
            cst = cst & ")(PORT=" & port
            cst = cst & ")))(CONNECT_DATA=(SERVICE_NAME=" & drv
            cst = cst & ")))"
            'ADD MIAO 2018/10/17 END
            If _con Is Nothing Then
                '_con = New OdbcConnection
                _con = New OracleConnection(cst)
            End If
            _con.ConnectionString = cst

            _con.Open()

            g_szDBIpAddress = _con.DataSource

            _connflg = True
            Connect = 1

            ConnectTimer.Start()
            it = 0

        Catch ex As Exception
            'Throw New Exception("Connect Error", ex)
            Connect = -1
            szMess = "SF データベースに接続できません" & vbCrLf & "Connect Error:" & ex.Message
            MsgBox(szMess)
        End Try
    End Function

    ''' 
    ''' DB切断
    ''' 
    Public Sub Disconnect()

        If False = _connflg Then
            Exit Sub
        End If

        Try
            _con.Close()
        Catch ex As Exception
            'Throw New Exception("Disconnect Error", ex)
            MsgBox("SF Disconnect Error:" & ex.Message)
        End Try
    End Sub

    ''' 
    ''' SQLの実行
    ''' 
    ''' SQL文
    ''' タイムアウト値
    ''' 
    ''' 
    Public Function ExecuteSql(ByVal sql As String, _
                               Optional ByVal tot As Integer = -1) As DataTable
        Dim dt As New DataTable

        If False = _connflg Then
            Return dt
            Exit Function
        End If
        'ADD MIAO 2018/10/16 Begin
        sql = formatSQL(sql)
        'ADD MIAO 2018/10/16 END
        Try
            CheckConnection()
            Dim sqlCommand As New OracleCommand(sql, _con) ', _trn)

            If tot > -1 Then
                sqlCommand.CommandTimeout = tot
            End If

            Dim adapter As New OracleDataAdapter(sqlCommand)

            adapter.Fill(dt)
            adapter.Dispose()
            sqlCommand.Dispose()
        Catch ex As Exception
            'Throw New Exception("ExecuteSql Error", ex)
            'MsgBox("ExecuteSql Error:" & ex.Message)
        End Try

        Return dt
    End Function

    ''' 
    ''' トランザクション開始
    ''' 
    ''' 
    Public Sub BeginTransaction()

        If False = _connflg Then
            Exit Sub
        End If

        Try
            CheckConnection()
            _trn = _con.BeginTransaction()
        Catch ex As Exception
            'Throw New Exception("BeginTransaction Error", ex)
            MsgBox("SF BeginTransaction Error:" & ex.Message)
        End Try
    End Sub

    ''' 
    ''' コミット
    ''' 
    ''' 
    Public Sub CommitTransaction()

        If False = _connflg Then
            Exit Sub
        End If

        Try
            CheckConnection()
            If _trn Is Nothing = False Then
                _trn.Commit()
            End If
            '>>2018/02/20 安部 トランザクション終了時に切断
            Disconnect()
            ConnectTimer.Stop()
            it = 0
            '<<2018/02/20 安部 トランザクション終了時に切断

        Catch ex As Exception
            'Throw New Exception("CommitTransaction Error", ex)
            MsgBox("SF CommitTransaction Error:" & ex.Message)
        Finally
            _trn = Nothing
        End Try
    End Sub

    ''' 
    ''' ロールバック
    ''' 
    ''' 
    Public Sub RollbackTransaction()

        If False = _connflg Then
            Exit Sub
        End If

        Try
            CheckConnection()
            If _trn Is Nothing = False Then
                _trn.Rollback()
            End If

            '>>2018/02/20 安部 トランザクション終了時に切断
            Disconnect()
            ConnectTimer.Stop()
            it = 0
            '<<2018/02/20 安部 トランザクション終了時に切断

        Catch ex As Exception
            'Throw New Exception("RollbackTransaction Error", ex)
            MsgBox("SF RollbackTransaction Error:" & ex.Message)
        Finally
            _trn = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' パラメータ付きのSELECT SQLの実行
    ''' </summary>
    ''' <param name="sql">SQL文</param>
    ''' <param name="tot">タイムアウト時間</param>
    ''' <param name="Params">パラメータ</param>
    ''' <returns>取得したテーブル</returns>
    ''' <remarks></remarks>
    Public Function ExecuteSql(ByVal sql As String, ByVal tot As Integer, _
                               ByVal ParamArray Params() As Object) As DataTable
        Dim dt As New DataTable

        If False = _connflg Then
            Return dt
            Exit Function
        End If

        'ADD MIAO 2018/10/16 Begin
        sql = formatSQL(sql)
        'ADD MIAO 2018/10/16 END

        Try
            CheckConnection()
            Dim sqlCommand As New OracleCommand(sql, _con) ', _trn)
            For Each obj In Params
                With sqlCommand
                    Dim param = .CreateParameter()
                    param.Value = obj.ToString
                    .Parameters.Add(param)
                End With
            Next

            If tot > -1 Then
                sqlCommand.CommandTimeout = tot
            End If

            Dim adapter As New OracleDataAdapter(sqlCommand)
            change_TIMESTAMP_FORMAT() 'ADD MIAO 2019/1/10
            adapter.Fill(dt)
            adapter.Dispose()
            sqlCommand.Dispose()
        Catch ex As Exception
            'Throw New Exception("ExecuteSql Error", ex)
            setErrLog(sql)
            MsgBox("SF ExecuteSql Error:" & ex.Message)
        End Try

        Return dt
    End Function

    ''' <summary>
    ''' DB更新系（INSERT、UPDATE、DELETE）のSQLの実行
    ''' </summary>
    ''' <param name="sql"></param>
    ''' <returns>影響を受けた行数</returns>
    ''' <remarks></remarks>
    Public Function ExecuteUpdateSql(ByVal sql As String, ByVal tot As Integer,
                               ByVal ParamArray Params() As Object) As Integer
        If False = _connflg Then
            Return 0
            Exit Function
        End If
        If sql.Contains("-2147483648") Then
            sql.Replace("-2147483648", "0")
        End If
        'ADD MIAO 2018/10/16 Begin
        sql = formatSQL(sql)
        'ADD MIAO 2018/10/16 END

        Try
            CheckConnection()
            Dim sqlCommand As New OracleCommand(sql, _con) ', _trn)
            For Each obj In Params
                With sqlCommand
                    Dim param = .CreateParameter()
                    param.Value = obj.ToString
                    param.Value = param.Value.ToString.Replace("-2147483648", "0")
                    .Parameters.Add(param)
                End With
            Next

            If tot > -1 Then
                sqlCommand.CommandTimeout = tot
            End If
            change_TIMESTAMP_FORMAT() 'ADD MIAO 2018/10/18
            Return sqlCommand.ExecuteNonQuery()
            sqlCommand.Dispose()
        Catch ex As Exception
            'Throw New Exception("ExecuteSql Error", ex)
            setErrLog(sql)
            MsgBox("SF ExecuteSql Error:" & ex.Message)
            Return -2
        End Try
    End Function


    Private Sub setErrLog(szMsg As String)
        Dim szSql As String = String.Empty
        szSql &= "INSERT INTO TRA.L_ICTAG_OPE_LOG( "
        szSql &= "  EQUIP_ID, KOUTEI_CD, LOT_NO, "
        szSql &= "  OPT_TIME, OPT_USER, CONTROL, "
        szSql &= "  MESSAGE "
        szSql &= ") "
        szSql &= "VALUES ( "
        szSql &= " ?, ?, ?, now() , ?, ? ,? "
        szSql &= ")"

        DBacs.ExecuteUpdateSql(szSql, -1, "SQLERR", "SQLERR", "SQLERR", "SQLERR", "SQLERR", szMsg)

    End Sub

    ''' <summary>
    ''' SQLを実行して結果の最初行と最終列を返す
    ''' </summary>
    ''' <param name="sql"></param>
    ''' <param name="tot"></param>
    ''' <param name="Params"></param>
    ''' <returns>結果の最初行と最終列の値</returns>
    ''' <remarks></remarks>
    Public Function ExccuteScalarSql(ByVal sql As String, ByVal tot As Integer, _
                               ByVal ParamArray Params() As Object) As Object
        If False = _connflg Then
            Return 0
            Exit Function
        End If

        'ADD MIAO 2018/10/16 Begin
        sql = formatSQL(sql)
        'ADD MIAO 2018/10/16 END

        Try
            CheckConnection()
            Dim sqlCommand As New OracleCommand(sql, _con) ', _trn)
            For Each obj In Params
                With sqlCommand
                    Dim param = .CreateParameter()
                    param.Value = obj.ToString
                    .Parameters.Add(param)
                End With
            Next

            If tot > -1 Then
                sqlCommand.CommandTimeout = tot
            End If
            change_TIMESTAMP_FORMAT() 'ADD MIAO 2018/10/18
            Return sqlCommand.ExecuteScalar()
            sqlCommand.Dispose()
        Catch ex As Exception
            'Throw New Exception("ExecuteSql Error", ex)
            setErrLog(sql)
            MsgBox("SF ExecuteSql Error:" & ex.Message)
            Return -2
        End Try
    End Function

    ''' 
    ''' ファイナライズ
    ''' 
    ''' 
    Protected Overrides Sub Finalize()

        If False = _connflg Then
            Exit Sub
        End If

        Disconnect()
        MyBase.Finalize()
    End Sub

    Private Sub CheckConnection()
        If _con.State = ConnectionState.Closed Then
            'Debug.Print(Date.Now.ToString("mm:ss:ffff"))
            _con.Open()
            'Debug.Print(Date.Now.ToString("mm:ss:ffff"))
            'Connection Timer Start
            ConnectTimer.Start()
            'Debug.Print("Connection=" & _con.State.ToString)
        End If
        it = 0
        'Debug.Print("Checked")
    End Sub

    Private Sub ConnectTimer_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles ConnectTimer.Tick
        '>>2018/02/20 安部 切断タイマを秒指定に変更
        'If it >= MINUTS_TO_DISCONNECT * 60 Then
        If it >= SECONDS_TO_DISCONNECT Then
            '<<2018/02/20 安部 切断タイマを秒指定に変更
            '>>2018/02/20 安部 it=0の処理順変更
            ' it = 0
            Disconnect()
            ConnectTimer.Stop()
            it = 0
            '>>2018/02/20 it=0の処理順変更
            'Debug.Print("Connection=" & _con.State.ToString)
        Else
            it += 1
        End If
    End Sub

    Private Function formatSQL(Sql As String) As String
        'SQLには「？」がある場合、「:数字」するようにフォーマットする
        If Sql.Contains("?") Then
            Dim intQ As Integer
            Dim strSQL As String()
            intQ = 1
            strSQL = Sql.Split(CType("?", Char()))
            Sql = ""
            For i = 0 To strSQL.Length - 1
                If strSQL(i).Trim <> "" Then
                    If i = strSQL.Length - 1 Then
                        Sql = Sql & strSQL(i)
                    Else
                        Sql = Sql & strSQL(i) & ":" & intQ
                        intQ += 1
                    End If
                End If
            Next
        End If

        'SQLには「 AS 」がある場合、「 」するようにフォーマットする
        If Sql.Contains(" AS ") Then
            Sql = Sql.Replace(" AS ", " ")
        End If

        'SQLには「False]の場合、「０」に変更
        'SQLには「True」の場合、[1]に変更
        'SQLには timestamp がある場合、空白に変更
        If Sql.ToUpper.Contains("FALSE") Then
            Sql = Sql.ToUpper.Replace("FALSE", "0")
        ElseIf Sql.ToUpper.Contains("TRUE") Then
            Sql = Sql.Replace("TRUE", "1")
        ElseIf Sql.ToUpper.Contains(" TIMESTAMP ") Then
            Sql = Sql.ToUpper.Replace(" TIMESTAMP ", "")
        End If

        'SQLの最後が[;]の場合、削除
        Sql = Sql.Trim
        If Sql.Substring(Sql.Trim.Length - 1) = ";" Then
            Sql = Sql.Substring(0, Sql.Length - 1)
        End If

        'SQLには timestamp がある場合、空白になる
        If Sql.Contains(" timestamp ") Then
            Sql = Sql.Replace(" timestamp ", "")
        ElseIf Sql.Contains("True") Then
            Sql = Sql.Replace("True", "1")
        End If

        If Sql.ToUpper.Contains(" LIMIT ") Then
            Dim strSQL As String
            Dim LimitSQL As String()
            Dim lmtNumLen As Integer = 1
            strSQL = Sql.ToUpper.Replace(" LIMIT ", "@")
            LimitSQL = strSQL.Split(CType("@", Char()))
            For i = 1 To LimitSQL.Length - 1
                Dim lmtNum As String
                'UPD MIAO 2018/10/30 BEGIN
                If LimitSQL(i).Trim.Contains(" ") = False Then
                    lmtNum = LimitSQL(i).Trim
                Else
                    lmtNum = LimitSQL(i).Trim.Split(CType(" ", Char()))(0)
                End If
                If lmtNum.Length > 1 Then
                    lmtNumLen = lmtNum.Length - 1
                End If
                'UPD MIAO 2018/10/30 END
                Dim WhereSQL1 As String
                WhereSQL1 = LimitSQL(i - 1).Trim.Replace(" WHERE ", "$")
                Dim whereSQL2 As String()
                whereSQL2 = WhereSQL1.Split(CType("$", Char()))
                whereSQL2(whereSQL2.Length - 1) = “ ROWNUM <= " & lmtNum & " AND " & whereSQL2(whereSQL2.Length - 1)
                Dim whereSQL3 As String
                whereSQL3 = ""
                For j = 0 To whereSQL2.Length - 1
                    whereSQL3 = whereSQL3 & whereSQL2(j) & "$"
                Next
                whereSQL3 = whereSQL3.Substring(0, whereSQL3.Length - 1)
                whereSQL3 = whereSQL3.Replace("$", " WHERE ")
                LimitSQL(i - 1) = whereSQL3
            Next
            strSQL = ""
            For j = 0 To LimitSQL.Length - 1
                If j = 0 Then
                    strSQL = strSQL & LimitSQL(j)
                Else
                    If lmtNumLen <> LimitSQL(j).Trim.Length - 1 Then
                        strSQL = strSQL & LimitSQL(j).Trim.Substring(lmtNumLen, LimitSQL(j).Trim.Length - 1)
                    End If
                End If
            Next
            Sql = strSQL
        End If

        formatSQL = Sql
    End Function
    'ADD MIAO 2018/10/18 Begin
    'TIMESTAMP_FORMATの変更
    Public Sub change_TIMESTAMP_FORMAT()
        If False = _connflg Then
            Exit Sub
        End If

        Dim sql As String
        Dim dt As DataTable
        sql = "select value from nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT'"
        dt = ExecuteSql(sql, -1)
        If dt.Rows(0)(0).ToString = "YYYY/MM/DD HH:mm:ss" Then
            Exit Sub
        End If

        sql = "alter session set NLS_TIMESTAMP_FORMAT='yyyy/mm/dd hh24:mi:ss:ff'"
        Try
            Dim sqlCommand As New OracleCommand(sql, _con)
            sqlCommand.ExecuteNonQuery()
            sqlCommand.Dispose()
        Catch ex As Exception
            'Throw New Exception("ExecuteSql Error", ex)
            setErrLog(sql)
            MsgBox("SF ExecuteSql Error:" & ex.Message)
        End Try

    End Sub

    'ADD MIAO 2018/10/18 End
End Class

